package edu.tongji.jiaowu.dao.jdbc;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import edu.tongji.jiaowu.dao.ScoreDao;
import com.tongji.quickCRUDQ.define.Row;
import com.tongji.quickCRUDQ.model.CommonBean;
import com.tongji.quickCRUDQ.util.StringUtil;
import com.tongji.quickCRUDQ.dao.jdbc.BaseDaoJdbc;

public class ScoreDaoJdbc extends BaseDaoJdbc implements ScoreDao {
	String tableName = "T_SCORE";

	String beanName = "score";

	public CommonBean createScore() {
		CommonBean scores = this.createBean(tableName, beanName);
		return scores;
	}

	/*
	 public CommonBean findScores(String refid) {
	 CommonBean scores=this.createScore();
	 String sql="select * from T_SCORE where refid=?";
	 List data=this.getCommonDao().select(sql,new String[]{refid});
	 scores.addData(data);
	 return scores;
	 }
	 */
	public CommonBean findScoreById(String id) {
		CommonBean scores = this.createScore();
		String sql = "select * from T_SCORE where id=?";
		List data = this.getCommonDao().select(sql, new String[] { id });
		scores.addData(data);
		return scores;
	}

	public CommonBean findAllScores() {
		CommonBean scores = this.createScore();
		String sql = "select * from T_SCORE ";
		List data = this.getCommonDao().select(sql, new String[] {});
		scores.addData(data);
		return scores;
	}
	
	public BigDecimal findGradePoint(String studentNo){
		String sql="select dbo.get_gradePoint(?) grade_point";
		Map map=getCommonDao().getOneRow(sql, new String[]{studentNo});
		String gradePoint=StringUtil.NVL((String)map.get("GRADE_POINT"),"0");
		return new BigDecimal(gradePoint);
	}
	
	public BigDecimal findGradePoint(String studentNo,String schoolYear,String schoolSemester){
		String sql="select dbo.get_gradePoint(?,?,?) grade_point";
		Map map=getCommonDao().getOneRow(sql, new String[]{studentNo,schoolYear,schoolSemester});
		String gradePoint=StringUtil.NVL((String)map.get("GRADE_POINT"),"0");
		return new BigDecimal(gradePoint);
	}
	
	public List findStudentScoreSemester(String studentNo){
		String sql=
			"select school_year,school_semester,grade_point,dbo.get_codeName('SCHOOL_YEAR',school_year) school_year_name,dbo.get_codeName('SCHOOL_SEMESTER',school_semester) school_semester_name  from\n" +
			" (\n" + 
			"   select school_year,school_semester,dbo.get_gradePoint_semester(?,school_year,school_semester)  grade_point\n" + 
			"   from (\n" + 
			"\t   select a.code school_year,b.code school_semester from v_code a,v_code b\n" + 
			"\t\t where a.category_code='SCHOOL_YEAR' and b.category_code='SCHOOL_SEMESTER'\n" + 
			"   ) tb\n" + 
			" ) as ta\n" + 
			" where grade_point is not null\n" + 
			"order by school_year,school_semester";
		List data=getCommonDao().select(sql, new String[]{studentNo});
		return data;
	}
	
	public List findStudentScore(String studentNo,String schoolYear,String schoolSemester){
		String sql=
			"select course_no,course_name,(case score_format when '02' then score else (select score_name from t_score_level where score_level=t_score.score) end ) score,\n" +
			"credit,is_pass,grade_point,dbo.get_codeName('SCORE_NATURE',score_nature) SCORE_NATURE,\n" + 
			"COURSE_TYPE,isnull(dbo.get_codeName('COURSE_TYPE',COURSE_TYPE),'必修') COURSE_TYPE,update_time\n" + 
			"from t_score\n" + 
			"where student_no=? and school_year=? and school_semester=?\n" + 
			"order by course_no";
		List data=getCommonDao().select(sql, new String[]{studentNo,schoolYear,schoolSemester});
		return data;
	}
	

	public void insertScore(CommonBean scoreBean) {
		List data = scoreBean.getData();
		Row row = this.getRow(tableName);
		this.getCommonDao().templateBatchInsert(row, data, this.getIdGenerator());
	}
	
	public void updateScore(CommonBean scoreBean){
		List data = scoreBean.getData();
		Row row = this.getRow(tableName);
		this.getCommonDao().templateBatchUpdate(row, data);
	}

	public CommonBean findScore(String courseNo, String studentNo) {
		CommonBean score = this.createScore();
		String sql = "select * from t_score where course_no=? and student_no=?";
		List data = this.getCommonDao().select(sql, new String[] { courseNo, studentNo });
		score.addData(data);
		return score;
	}

	public BigDecimal findSumCredit(String studentNo) {
		String sql="select sum(credit) cnt from t_score where student_no=?";
		Map row=getCommonDao().getOneRow(sql,new String[]{studentNo});
		String sumCredit=(String) row.get("CNT");
		return new BigDecimal(sumCredit);
	}

	public BigDecimal findPassedCredit(String studentNo) {
		String sql="select sum(credit) cnt from t_score where is_pass='Y'  and student_no=?";
		Map row=getCommonDao().getOneRow(sql,new String[]{studentNo});
		String sumCredit=StringUtil.NVL((String) row.get("CNT"),"0");
		return new BigDecimal(sumCredit);
	}

	public int findFailedCourseCount(String studentNo) {
		String sql="select count(*) cnt from t_score where is_pass='N'  and student_no=?";
		Map row=getCommonDao().getOneRow(sql,new String[]{studentNo});
		String cnt=StringUtil.NVL((String) row.get("CNT"),"0");
		return new Integer(cnt);
	}

	public void deleteScore(List deleteScore) {
		String sql="delete from t_score where student_no=? and course_no=? ";
		getCommonDao().batchUpdateRecord(sql, deleteScore);
	}

	public void maskScorePrint(String studentNo) {
		String sql="update _student_score_print set cj='未通过',cjxzmc='未通过' where xh=? and sftg='N' ";
		getCommonDao().updateRecord(sql, new String[]{studentNo});
	}

	public void maskScorePrint(String grade, String majorNo) {
//		String sql="update _student_score_print set cj='未通过',cjxzmc='未通过' where xh=? and sftg='N' ";
		String sql="update _student_score_print set cj='未通过',cjxzmc='未通过' where exists( "+
				  " select * from t_student where grade=? and major=? and student_no=_student_score_print.xh "+
				  ")  and sftg='N'";			
		getCommonDao().updateRecord(sql, new String[]{grade,majorNo});
		
	}
/**
 * 毕业生可删选修课:已修满8学分的公选课,但还有不及格的公选课,则可删除
 */
	public void deleteFailElective() {
		String sql="delete from t_score where exists(select * from V_FAIL_ELECTIVE where id=t_score.id)";
		getCommonDao().updateRecord(sql, null);
	}

}
